OpenEdge Data Management:<br />SQL Development Using the SELECT statement

Release 10.1A: OpenEdge Data Management:
SQL Development

Using the SELECT statement

Use the SELECT statement to retrieve information from a database.

The SELECT statement uses the following syntax:
Syntax
SELECT [ ALL | DISTINCT ]
  { * 
    | { table_name | alias.} * [ , { table_name.| alias.} * ] ... 
    | expr [ [ AS ] [ ’ ] column_title [ ’ ] ] 
        [, expr [ [ AS ] [ ’ ] column_title [ ’ ] ] ] ... 
  }
FROM table_ref [, table_ref ] ... [ { NO REORDER } ]
  [ WHERE search_condition ]
  [ GROUP BY [ table.]column_name [, [ table.]column_name ] ...
  [ HAVING search_condition ] ; 
The SELECT statement provides you with countless ways to retrieve and analyze the data in your database. Queries vary from simple to sophisticated. They can retrieve information from a single column of a single table, or they can retrieve data meeting specific conditions from many columns across many tables.

The following statements offer just a few examples of how the SELECT statement can be used to create queries.

In Example 6–1, the simple SELECT statement retrieves all columns from the Customer table.
Example 6–1: SELECT statement on entire table
SELECT * FROM Customer; 
In Example 6–2, the statement is easily modified to identify columns from which the data will be retrieved.
Example 6–2: SELECT statement with columns identified
Select CustNum, Name, City FROM Customer; 
The simple SELECT - FROM combination can even be used to retrieve a single set of results from multiple tables. Example 6–3 retrieves the customer and order information from both the Customer and Order tables.
Example 6–3: SELECT statement from multiple tables
SELECT Customer.CustNum, Customer.Name, Order.OrderNum, Order.OrderDate  
     FROM Customer, Order;  
The WHERE clause can be used to further refine your query. In Example 6–4, the FROM clause uses the WHERE, GROUP BY, and HAVING conditions to create a highly specific query statement. The query returns the customer number and number of orders for all customers who had more than 10 orders before March 31, 2003.
Example 6–4: SELECT statement using WHERE clause
SELECT CustNum, COUNT(*)  
     FROM Order 
     WHERE OrderDate < '3/31/03' 
     GROUP BY CustNum 
     HAVING COUNT (*) > 10 ; 
Notes: The WHERE clause limits a query to retrieving specified rows based upon a search condition. The GROUP BY clause produces a summary query in which similar rows are grouped together.The HAVING clause further restricts the GROUP BY clause by allowing only those groups that have been specified by a search condition.

OpenEdge SQL can access all databases objects created with Progress 4GL. The Progress 4GL can access SQL-created objects, but they must contain data types recognized by 4GL and must reside in the PUB schema of an OpenEdge RDBMS.